package demo.database.mssql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

import com.titan.core.utils.db.DBUtils;
import com.titan.core.utils.db.RecordSet;
import com.titan.core.utils.file.FileUtils;

/**
 *
 *
 * @author 周成功
 * 
 */
public class ExportTable {
    public static void main(String[] args) {
        String[][] config = new String[][] {
//                {"oracle", "127.0.0.1", "1521", "ORCL", "etms3_base", "etms3_base"},
//                {"oracle", "127.0.0.1", "1521", "ORCL", "etms3", "etms3"}
//                {"oracle", "10.54.121.250", "1521", "CNNPS", "nbas", "nbas"}
                {"mssql", "192.168.128.213", "1433", "H20170913", "sa", "123456"}
//                {"oracle", "192.168.128.212", "1521", "ORCL", "hrbsgts", "hrbsgts"}
//            {"mysql", "127.0.0.1", "3306", "play", "root", "123456"}
        };
        String filepath = "D:\\我的项目\\项目-张宁\\Workspace\\MemberServer\\db\\";
        
        Connection conn = null;
        try {
            for (int i = 0; i < config.length; i++) {
                String sql = null;
                if("oracle".equalsIgnoreCase(config[i][0])) {
                    sql = "select t.tname from tab t where t.tabtype='TABLE' and tname not in (SELECT object_name FROM user_recyclebin)";
                    conn = DBUtils.getOracleConnection(config[i][1], config[i][2], config[i][3], config[i][4], config[i][5], true);
                } else if("mysql".equalsIgnoreCase(config[i][0])) {
                    sql = "select table_name tname, table_comment from information_schema.tables where upper(table_schema) = upper('"+config[i][3]+"') and upper(table_type)='BASE TABLE'";
                    conn = DBUtils.getMySQLConnection(config[i][1], config[i][2], config[i][3], config[i][4], config[i][5], true);
                } else if("mssql".equalsIgnoreCase(config[i][0])) {
                    sql = "select name tname from sys.tables";
                    conn = DBUtils.getMSSQLConnection(config[i][1], config[i][2], config[i][3], config[i][4], config[i][5], true);
                } else {
                    throw new RuntimeException("不知道的数据库类型["+config[i][0]+"]！");
                }
                
                RecordSet record = DBUtils.executeQueryToRecordSet(conn, sql);
                List<String> result = new ArrayList<String>();
                while(record.moveNext()) {
                    String tablename = record.getString("tname");
                    
                    StringBuilder create = new StringBuilder(1024);
                    create.append("DROP TABLE IF EXISTS `").append(tablename).append("`;").append("\n");
                    create.append("CREATE TABLE `").append(tablename).append("` (").append("\n");
                    create.append("    `id`              varchar(100)     NOT NULL          COMMENT '编号',").append("\n");
                    
                    String select = null;
                    if("mssql".equalsIgnoreCase(config[i][0])) {
                        select = "select * from [" + tablename + "]";
                    } else {
                        select = "select * from " + tablename + "";
                    }
                    
                    PreparedStatement stmt = null;
                    ResultSet rest = null;
                    try {
                        stmt = conn.prepareStatement(select);
                        rest = stmt.executeQuery();
                        ResultSetMetaData data = rest.getMetaData();
                        for (int j = 1; j <= data.getColumnCount(); j++) {
                            String columnName = data.getColumnName(j);  // 获得指定列的列名
                            if("id".equalsIgnoreCase(columnName)) {
                                continue;
                            }
                            if("memo".equalsIgnoreCase(columnName)) {
                                continue;
                            }
                            
                            String columnTypeName = data.getColumnTypeName(j);  // 获得指定列的数据类型名
                            int precision = data.getPrecision(j);  // 某列类型的精确度(类型的长度)
                            int scale = data.getScale(j);  // 小数点后的位数
                            
                            create.append("    `").append(columnName).append("`");
                            if("int".equalsIgnoreCase(columnTypeName)) {
                                create.append(" int(").append(precision).append(")  ");
                            } else if("bigint".equalsIgnoreCase(columnTypeName)) {
                                create.append(" bigint(").append(precision).append(")  ");
                            } else if("money".equalsIgnoreCase(columnTypeName)) {
                                create.append(" double(").append(precision).append(",").append(scale).append(")  ");
                            } else if("decimal".equalsIgnoreCase(columnTypeName)) {
                                create.append(" double(").append(precision).append(",").append(scale).append(")  ");
                            } else if("datetime".equalsIgnoreCase(columnTypeName)) {
                                create.append(" datetime  ");
                            } else if("nvarchar".equalsIgnoreCase(columnTypeName)) {
                                if(precision>4000) {
                                    create.append(" longtext  ");
                                } else {
                                    create.append(" varchar(").append(precision).append(")  ");
                                }
                            } else if("varchar".equalsIgnoreCase(columnTypeName)) {
                                if(precision>4000) {
                                    create.append(" longtext  ");
                                } else {
                                    create.append(" varchar(").append(precision).append(")  ");
                                }
                            } else if("text".equalsIgnoreCase(columnTypeName)) {
                                create.append(" longtext  ");
                            } else if("nchar".equalsIgnoreCase(columnTypeName)) {
                                create.append(" char(").append(precision).append(")  ");
                            } else if("bit".equalsIgnoreCase(columnTypeName)) {
                                create.append(" char(").append(precision).append(")  ");
                            } else {
                                System.out.println("表名："+tablename);
                                System.out.println("获得列" + j + "类型的精确度(类型的长度):" + data.getPrecision(j));
                                System.out.println("获得列" + j + "小数点后的位数:" + data.getScale(j));
                                System.out.println("获得列" + j + "是否为空:" + data.isNullable(j));
                                throw new Exception("未知的列类型："+data.getColumnTypeName(j));
                            }
                            create.append(" DEFAULT NULL COMMENT '',").append("\n");
                        }
                    } finally {
                        DBUtils.close(rest);
                        DBUtils.close(stmt);
                    }
                    create.append("    `memo`            longtext         DEFAULT NULL      COMMENT '备注',").append("\n");
                    create.append("    `t1`              varchar(100)     DEFAULT NULL      COMMENT '附加属性01',").append("\n");
                    create.append("    `t2`              varchar(100)     DEFAULT NULL      COMMENT '附加属性02',").append("\n");
                    create.append("    `t3`              varchar(100)     DEFAULT NULL      COMMENT '附加属性03',").append("\n");
                    create.append("    `t4`              varchar(100)     DEFAULT NULL      COMMENT '附加属性04',").append("\n");
                    create.append("    `t5`              varchar(100)     DEFAULT NULL      COMMENT '附加属性05',").append("\n");
                    create.append("    `create_name`     varchar(100)     NOT NULL          COMMENT '记录创建人',").append("\n");
                    create.append("    `create_dt`       datetime         NOT NULL          COMMENT '记录创建时间',").append("\n");
                    create.append("    `update_name`     varchar(100)     NOT NULL          COMMENT '记录最后修改人',").append("\n");
                    create.append("    `update_dt`       datetime         NOT NULL          COMMENT '记录最后修改时间',").append("\n");
                    create.append("    `delete_flag`     varchar(30)      NOT NULL          COMMENT '记录删除标识',").append("\n");
                    create.append("    `delete_name`     varchar(100)     DEFAULT NULL      COMMENT '记录删除人',").append("\n");
                    create.append("    `delete_dt`       datetime         DEFAULT NULL      COMMENT '记录删除时间',").append("\n");
                    create.append("    PRIMARY KEY (`id`)").append("\n");
                    create.append(") ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='';").append("\n");

//                    System.out.println(create);
                    FileUtils.write(filepath, tablename+".sql", create.toString(), "UTF-8", false);
                }
                DBUtils.close(conn);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(conn);
        }
    }

}

